<?php
// 自动加载
require './src/Spout/Autoloader/autoload.php';

use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

// 不限制最长运行时间
ini_set('max_execution_time',0);

$reader = ReaderFactory::create(Type::XLSX);

$reader->open('./17w2.xlsx');

$i = 0;
$fields = 'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,ab,
    ac,ad,ae,af,ag,ah,ai,aj,ak,al,am,an,ao,ap,aq,ar,cs,ct,au,av,aw,ax,ay,az,
    ba,bb,bc,bd,be,bf,bg,bh,bi,bj,bk,bl,bm,bn,bo,bp';

// $startTime = time();
// 连接 MySQL
$mysqli = new mysqli('localhost','root','','test');
if(mysqli_connect_error()){
    throw new Exception('MySql connent failure!');
}
$v = 0;
$insetValue = '';
// 遍历所有数据
foreach($reader->getSheetIterator() as $sheet){
    if($sheet->isActive()){
        $rowIterator = $sheet->getRowIterator();
        $j = 0;
        $z = 0;
        //var_dump($rowIterator);
        // 这个循环遍历了一次
        foreach($rowIterator as $row) {

            $j += 1;
            $z += 1;
            // 插入前 1w 条数据
            if ($z <= 160000) {
                // 每一千条插入一次
                if ($j % 1000 != 0) {
                    $insetValue .= ',(\'' . implode('\',\'', $row) . '\')';
                } else {
                    //$insetValue .= ',(\'' . implode('\',\'', $row) . '\')';
                    $sql = 'INSERT INTO test (' . $fields . ') VALUES ' . substr($insetValue, 1) . ';';
                    //echo $sql;
                    $mysqli->query($sql);
                    $insetValue = '';           // 执行过后清空
                }
            } else {
                // 关闭MySQL连接
                mysqli_close($mysqli);
                // 计算并输出所需时间
                // $endTime = time();
                // $needTime = $endTime - $startTime;       // 单位为秒
                // echo '总共需要:' . $needTime . 's';
                // die();
            }
        }
    }
}
